using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using Infrastructure;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;
using NHibernate.SqlCommand;
using NHibernate.Transform;
using WcfService1.DTO;
using Expression = NHibernate.Criterion.Expression;

namespace Infrastucture
{
    public class InventoryRepository : BaseNHibernateRepository<Inventory>
    {
        private DataAccess<Inventory> DA;
        private InventoryWarehouseRepository iwRepository;
        private MutasiStokRepository mutasiRepository;
       
        public InventoryRepository(ISessionStorage sessionStorage) : base(sessionStorage)
        {
            iwRepository = new InventoryWarehouseRepository(sessionStorage);
            mutasiRepository = new MutasiStokRepository(sessionStorage);
        }

        public override IList<Inventory> GetDataPaged(int page, int pageSize, string sortProperty, string sortType)
        {
           var Results = new List<Inventory>();
                
                        using (ITransaction tx = Session.BeginTransaction())
                        {
                            INHibernateQueryable<Inventory> query = Session.Linq<Inventory>();
                            ICriteria criteria = Session.CreateCriteria<Inventory>()
                                .CreateAlias("Item", "it", JoinType.InnerJoin)
                                .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                                .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                                .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                                .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                                .CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                                //.SetProjection(Projections.Distinct(Projections.ProjectionList()
                                .SetProjection((Projections.ProjectionList()
                                                                        .Add(Projections.Property("SerialNumber"),
                                                                             "SerialNumber")
                                                                        .Add(Projections.Property("NoBundle"),
                                                                             "NoBundle")
                                                                        .Add(Projections.Property("Description"),
                                                                             "Description")
                                                                        .Add(Projections.Property("Panjang"), "Panjang")
                                                                        .Add(Projections.Property("Lebar"), "Lebar")
                                                                        .Add(Projections.Property("Tinggi"), "Tinggi")
                                                                        .Add(Projections.Property("QtyPack"), "QtyPack")
                                                                        .Add(Projections.Property("QtyUnit"), "QtyUnit")
                                                                        .Add(Projections.Property("UnitPrice"),
                                                                             "UnitPrice")
                                                                        .Add(Projections.Property("CoaHpp"), "CoaHpp")
                                                                        .Add(Projections.Property("CoaInventory"),
                                                                             "CoaInventory")
                                                                        .Add(Projections.Property("CoaSales"),
                                                                             "CoaSales")
                                                                        .Add(Projections.Property("CoaSalesReturn"),
                                                                             "CoaSalesReturn")
                                                                        .Add(Projections.Property("it.ItemName"),
                                                                             "ItemName")
                                                                        .Add(
                                                                            Projections.Property("SatuanPack.KodeSatuan"),
                                                                            "SatuanPack")
                                                                        .Add(
                                                                            Projections.Property("SatuanUnit.KodeSatuan"),
                                                                            "SatuanUnit")
                                                                        .Add(Projections.Property("g.GroupName"),
                                                                             "GroupName")
                                                                        .Add(Projections.Property("sg.SubGroupName"),
                                                                             "SubGroupName")
                                                                        .Add(Projections.Property("iw.KodeWarehouse"),
                                                                             "KodeWarehouse")
                                                                        .Add(Projections.Property("iw.Qty"), "Qty")
                                                                        .Add(Projections.Property("iw.Sheet"), "Sheet")
                                                                        .Add(Projections.Property("iw.KodePallet"),
                                                                             "KodePallet")
                                                                        .Add(
                                                                            Projections.Property("SatuanUnit.Pengali"),
                                                                            "Pengali")
                                                   ));

                            if (sortType.ToUpper() == "ASC")
                                criteria.AddOrder(new Order(sortProperty, true));
                            else
                                criteria.AddOrder(new Order(sortProperty,false));

                                criteria.SetFirstResult((page-1)*pageSize).SetMaxResults(pageSize);

                            ICriteria criteriacount =
                                Session.CreateCriteria<Inventory>()
                                .CreateAlias("Item", "it", JoinType.InnerJoin)
                                .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                                .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                                .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                                .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                                .CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                                .SetResultTransformer(new DistinctRootEntityResultTransformer()) 
                                .SetProjection(Projections.RowCountInt64());
                            IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount).List();

                            Dictionary<string, string> dicts = new Dictionary<string, string>();
                            foreach (object o in (IList)Res[0])
                            {

                                //if (!dicts.ContainsKey((o as object[])[0].ToString()))
                                //{
                                //    dicts.Add((o as object[])[0].ToString(), (o as object[])[0].ToString());

                                    Inventory inv = new Inventory();
                                    inv.SerialNumber = (o as object[])[0].ToString();
                                    inv.NoBundle = (int)(o as object[])[1];
                                    inv.Description = (String)(o as object[])[2];
                                    inv.Panjang = (Decimal)(o as object[])[3];
                                    inv.Lebar = (Decimal)(o as object[])[4];
                                    if ((o as object[])[20] != null && (Decimal)(o as object[])[20] > 0)
                                        inv.Tinggi = (Decimal)(o as object[])[20];
                                    else
                                        inv.Tinggi = (Decimal)(o as object[])[5];
                                    inv.QtyPack = (Decimal)(o as object[])[6];
                                    if ((o as object[])[19] != null && (Decimal)(o as object[])[19] > 0)
                                        inv.QtyUnit = (Decimal)(o as object[])[19];
                                    else
                                        inv.QtyUnit = (Decimal)(o as object[])[7];
                                    inv.UnitPrice = (Decimal)(o as object[])[8];
                                    inv.CoaHpp = (String)(o as object[])[9];
                                    inv.CoaInventory = (String)(o as object[])[10];
                                    inv.CoaSales = (String)(o as object[])[11];
                                    inv.CoaSalesReturn = (String)(o as object[])[12];
                                    inv.Item = new Item()
                                    {
                                        ItemName = (String)(o as object[])[13],
                                        SubGroup = new SubGroup()
                                        {
                                            SubGroupName = (String)(o as object[])[17],
                                            Group = new Group() { GroupName = (String)(o as object[])[16] }
                                        }
                                    };
                                    inv.SatuanPack = new Satuan() { KodeSatuan = (String)(o as object[])[14] };
                                    inv.SatuanUnit = new Satuan() { KodeSatuan = (String)(o as object[])[15], Pengali = (o as object[])[22] == null ? 1 : (Decimal)(o as object[])[22] };
                                    inv.InventoryWarehouses = new List<InventoryWarehouse>() { new InventoryWarehouse()
                                                                                       {
                                                                                           SerialNumber = (o as object[])[0].ToString(), 
                                                                                           KodeWarehouse = (String)(o as object[])[18],
                                                                                           Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                                                                                           Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                                                                                           KodePallet = (String)(o as object[])[21]
                                                                                       } };
                                    Results.Add(inv);
                                }
                            //    else
                            //    {
                            //        Results.LastOrDefault().InventoryWarehouses.Add(new InventoryWarehouse()
                            //        {
                            //            SerialNumber = (o as object[])[0].ToString(),
                            //            KodeWarehouse = (String)(o as object[])[18],
                            //            Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                            //            Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                            //            KodePallet = (String)(o as object[])[21]
                            //        });
                            //    }
                            //}
                            RecordCount = (long) ((IList) Res[1])[0];
                            tx.Commit();
                        }
               
                 return Results;
        }


        public override IList<Inventory> GetDataPaged(int page, int pageSize)  
        {
            var Results = new List<Inventory>();
            using (ITransaction tx = Session.BeginTransaction())
            {
                ICriteria criteria = Session.CreateCriteria<Inventory>("inv")
                    .CreateAlias("Item", "it", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                    .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                    .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                    .CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                    .SetProjection((Projections.ProjectionList()
                                   .Add(Projections.Property("inv.SerialNumber"), "SerialNumber")
                                   .Add(Projections.Property("inv.NoBundle"), "NoBundle")
                                   .Add(Projections.Property("inv.Description"), "Description")
                                   .Add(Projections.Property("inv.Panjang"), "Panjang")
                                   .Add(Projections.Property("inv.Lebar"), "Lebar")
                                   .Add(Projections.Property("inv.Tinggi"), "Tinggi")
                                   .Add(Projections.Property("inv.QtyPack"), "QtyPack")
                                   .Add(Projections.Property("inv.QtyUnit"), "QtyUnit")
                                   .Add(Projections.Property("inv.UnitPrice"), "UnitPrice")
                                   .Add(Projections.Property("inv.CoaHpp"), "CoaHpp")
                                   .Add(Projections.Property("inv.CoaInventory"), "CoaInventory")
                                   .Add(Projections.Property("inv.CoaSales"), "CoaSales")
                                   .Add(Projections.Property("inv.CoaSalesReturn"), "CoaSalesReturn")
                                   .Add(Projections.Property("it.ItemName"), "ItemName")
                                   .Add(Projections.Property("SatuanPack.KodeSatuan"), "SatuanPack")
                                   .Add(Projections.Property("SatuanUnit.KodeSatuan"), "SatuanUnit")
                                   .Add(Projections.Property("g.GroupName"), "Group")
                                   .Add(Projections.Property("sg.SubGroupName"), "Subgroup")
                                   .Add(Projections.Property("iw.KodeWarehouse"), "KodeWarehouse")
                                   .Add(Projections.Property("iw.Qty"), "Qty")
                                   .Add(Projections.Property("iw.Sheet"), "Sheet")
                                   .Add(Projections.Property("iw.KodePallet"), "KodePallet")
                                   .Add(Projections.Property("SatuanUnit.Pengali"), "Pengali")
                                   )

                     )
                    .AddOrder(new Order("it.ItemName", true))
                    .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

                ICriteria criteriacount =
                    Session.CreateCriteria<Inventory>()
                    .CreateAlias("Item", "it", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                    .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                    .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                    .CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .SetProjection(Projections.RowCountInt64());
                //IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount).List();

                var tes = criteria.Future<IList>();
                var recCount = criteriacount.FutureValue<long>();

                Dictionary<string,string> dicts = new Dictionary<string, string>();

                foreach (object o in tes)
                {

                    //if (!dicts.ContainsKey((o as object[])[0].ToString()))
                    //{
                    //    dicts.Add((o as object[])[0].ToString(), (o as object[])[0].ToString());

                        Inventory inv = new Inventory();
                        inv.SerialNumber = (o as object[])[0].ToString();
                        inv.NoBundle = (int)(o as object[])[1];
                        inv.Description = (String)(o as object[])[2];
                        inv.Panjang = (Decimal)(o as object[])[3];
                        inv.Lebar = (Decimal)(o as object[])[4];
                        if ((o as object[])[20] != null && (Decimal)(o as object[])[20] > 0)
                            inv.Tinggi = (Decimal)(o as object[])[20];
                        else
                            inv.Tinggi = (Decimal)(o as object[])[5];
                        inv.QtyPack = (Decimal)(o as object[])[6];
                        if ((o as object[])[19] != null && (Decimal)(o as object[])[19] > 0)
                            inv.QtyUnit = (Decimal)(o as object[])[19];
                        else
                            inv.QtyUnit = (Decimal)(o as object[])[7];
                        inv.UnitPrice = (Decimal)(o as object[])[8];
                        inv.CoaHpp = (String)(o as object[])[9];
                        inv.CoaInventory = (String)(o as object[])[10];
                        inv.CoaSales = (String)(o as object[])[11];
                        inv.CoaSalesReturn = (String)(o as object[])[12];
                        inv.Item = new Item()
                        {
                            ItemName = (String)(o as object[])[13],
                            SubGroup = new SubGroup()
                            {
                                SubGroupName = (String)(o as object[])[17],
                                Group = new Group() { GroupName = (String)(o as object[])[16] }
                            }
                        };
                        inv.SatuanPack = new Satuan() { KodeSatuan = (String)(o as object[])[14] };
                        inv.SatuanUnit = new Satuan() { KodeSatuan = (String)(o as object[])[15], Pengali = (o as object[])[22] == null ? 1 : (Decimal)(o as object[])[22] };
                        inv.InventoryWarehouses = new List<InventoryWarehouse>() { new InventoryWarehouse()
                                                                                       {
                                                                                           SerialNumber = (o as object[])[0].ToString(), 
                                                                                           KodeWarehouse = (String)(o as object[])[18],
                                                                                           Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                                                                                           Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                                                                                           KodePallet = (String)(o as object[])[21]
                                                                                       } };
                        Results.Add(inv);
                    //}
                    //else
                    //{
                    //    Results.LastOrDefault().InventoryWarehouses.Add(new InventoryWarehouse()
                    //    {
                    //        SerialNumber = (o as object[])[0].ToString(),
                    //        KodeWarehouse = (String)(o as object[])[18],
                    //        Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                    //        Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                    //        KodePallet = (String)(o as object[])[21]
                    //    });
                    //}
                }


                RecordCount = recCount.Value;  //(long)((IList)Res[1])[0];
                tx.Commit();
            }
            return Results;

          
        }

        public override IList<Inventory> GetDataList(Expression<Func<Inventory, bool>> Criteria)
        {
            IList<Inventory> Result = new List<Inventory>();
            using (ITransaction tx = Session.BeginTransaction())
            {
                Result = Session.Linq<Inventory>().Where(Criteria).Take(50).ToList();
                tx.Commit();
            }

            return Result;
        }

        public override IList<Inventory> GetDataPaged(Dictionary<String, Object> Criteria, string expression, int page,
                                                      int pageSize)
        {
            var Results = new List<Inventory>();
            using (ITransaction tx = Session.BeginTransaction())
            {
                INHibernateQueryable<Inventory> query = Session.Linq<Inventory>();
                ICriteria criteria = Session.CreateCriteria<Inventory>()
                    .CreateAlias("Item", "it", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                    .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                    .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                    .CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                    .SetProjection((Projections.ProjectionList()
                                   .Add(Projections.Property("SerialNumber"), "SerialNumber")
                                   .Add(Projections.Property("NoBundle"), "NoBundle")
                                   .Add(Projections.Property("Description"), "Description")
                                   .Add(Projections.Property("Panjang"), "Panjang")
                                   .Add(Projections.Property("Lebar"), "Lebar")
                                   .Add(Projections.Property("Tinggi"), "Tinggi")
                                   .Add(Projections.Property("QtyPack"), "QtyPack")
                                   .Add(Projections.Property("QtyUnit"), "QtyUnit")
                                   .Add(Projections.Property("UnitPrice"), "UnitPrice")
                                   .Add(Projections.Property("CoaHpp"), "CoaHpp")
                                   .Add(Projections.Property("CoaInventory"), "CoaInventory")
                                   .Add(Projections.Property("CoaSales"), "CoaSales")
                                   .Add(Projections.Property("CoaSalesReturn"), "CoaSalesReturn")
                                   .Add(Projections.Property("it.ItemName"), "ItemName")
                                   .Add(Projections.Property("SatuanPack.KodeSatuan"), "SatuanPack")
                                   .Add(Projections.Property("SatuanUnit.KodeSatuan"), "SatuanUnit")
                                   .Add(Projections.Property("g.GroupName"), "Group")
                                   .Add(Projections.Property("sg.SubGroupName"), "Subgroup")
                                   .Add(Projections.Property("iw.KodeWarehouse"), "KodeWarehouse")
                                   .Add(Projections.Property("iw.Qty"), "Qty")
                                   .Add(Projections.Property("iw.Sheet"), "Sheet")
                                   .Add(Projections.Property("iw.KodePallet"), "KodePallet")
                                   .Add(Projections.Property("SatuanUnit.Pengali"), "Pengali")
                                   ))
                    .AddOrder(new Order("it.ItemName", true))
                    .SetFirstResult((page-1)*pageSize).SetMaxResults(pageSize);


                var filter = new List<string>();
                int i = 0;
                foreach (var criterion in Criteria)
                    if (
                        !typeof(Inventory).GetProperties().Any(
                            x => x.Name == criterion.Key.Split(new[] { "." }, StringSplitOptions.None)[0]))
                        filter.Add(criterion.Key);

                filter.ForEach((x) => { Criteria.Remove(x); }
                    );


                dynamic exp = null;
                if (expression == "AND")
                    exp = Expression.Conjunction();

                else
                    exp = Expression.Disjunction();

                foreach (var criterion in Criteria)
                {
                    if (criterion.Value == null) continue;
                    if (criterion.Key.Contains("Item"))
                    {
                        exp.Add(Expression.Eq("it.ItemId", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("Warehouse"))
                    {
                        exp.Add(Expression.Eq("iw.KodeWarehouse", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("SatuanUnit"))
                    {
                        exp.Add(Expression.Eq("SatuanUnit.KodeSatuan", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("SatuanPack"))
                    {
                        exp.Add(Expression.Eq("SatuanPack.KodeSatuan", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Value.GetType() == typeof(string))
                        exp.Add(Expression.Like(criterion.Key, criterion.Value.ToString(), MatchMode.Anywhere));
                    else
                    {
                        if (criterion.Key == "IsStock")
                            exp.Add(Expression.Conjunction().Add((Expression.Eq(criterion.Key, criterion.Value))));
                        else
                            exp.Add(Expression.Eq(criterion.Key, criterion.Value));
                    }
                    i += 1;
                }

                criteria.Add(exp);

                    ICriteria criteriaCount = Session.CreateCriteria<Inventory>()
                    .CreateAlias("Item", "it", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                    .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                    .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                    //.CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                    .Add(exp)
                    .SetResultTransformer(new DistinctRootEntityResultTransformer())
                    .SetProjection(Projections.RowCountInt64());

                var Res = criteria.List();
                //Dictionary<string,string> dicts = new Dictionary<string, string>();
                foreach (object o in (IList)Res)
                {

                    //if (!dicts.ContainsKey((o as object[])[0].ToString()))
                    //{
                    //    dicts.Add((o as object[])[0].ToString(), (o as object[])[0].ToString());

                        Inventory inv = new Inventory();
                        inv.SerialNumber = (o as object[])[0].ToString();
                        inv.NoBundle = (int)(o as object[])[1];
                        inv.Description = (String)(o as object[])[2];
                        inv.Panjang = (Decimal)(o as object[])[3];
                        inv.Lebar = (Decimal)(o as object[])[4];
                        if ((o as object[])[20] != null && (Decimal)(o as object[])[20] > 0)
                            inv.Tinggi = (Decimal)(o as object[])[20];
                        else
                            inv.Tinggi = (Decimal)(o as object[])[5];
                        inv.QtyPack = (Decimal)(o as object[])[6];
                        if ((o as object[])[19] != null && (Decimal)(o as object[])[19] > 0)
                            inv.QtyUnit = (Decimal)(o as object[])[19];
                        else
                            inv.QtyUnit = (Decimal)(o as object[])[7];
                        inv.UnitPrice = (Decimal)(o as object[])[8];
                        inv.CoaHpp = (String)(o as object[])[9];
                        inv.CoaInventory = (String)(o as object[])[10];
                        inv.CoaSales = (String)(o as object[])[11];
                        inv.CoaSalesReturn = (String)(o as object[])[12];
                        inv.Item = new Item()
                        {
                            ItemName = (String)(o as object[])[13],
                            SubGroup = new SubGroup()
                            {
                                SubGroupName = (String)(o as object[])[17],
                                Group = new Group() { GroupName = (String)(o as object[])[16] }
                            }
                        };
                        inv.SatuanPack = new Satuan() { KodeSatuan = (String)(o as object[])[14] };
                        inv.SatuanUnit = new Satuan() { KodeSatuan = (String)(o as object[])[15], Pengali = (o as object[])[22] == null ? 1 : (Decimal)(o as object[])[22] };
                        inv.InventoryWarehouses = new List<InventoryWarehouse>() { new InventoryWarehouse()
                                                                                       {
                                                                                           SerialNumber = (o as object[])[0].ToString(), 
                                                                                           KodeWarehouse = (String)(o as object[])[18],
                                                                                           Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                                                                                           Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                                                                                           KodePallet = (String)(o as object[])[21]
                                                                                       } };
                        Results.Add(inv);
                    //}
                    //else
                    //{
                    //    Results.LastOrDefault().InventoryWarehouses.Add(new InventoryWarehouse()
                    //    {
                    //        SerialNumber = (o as object[])[0].ToString(),
                    //        KodeWarehouse = (String)(o as object[])[18],
                    //        Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                    //        Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                    //        KodePallet = (String)(o as object[])[21]
                    //    });
                    //}
                }


                RecordCount = (long)criteriaCount.List()[0];
                tx.Commit();
            }

            return Results;
        }



        //Untuk searching Inventory di setiap transaksi
        public List<InventoryDTO> GetDataByCriteriaFull(Dictionary<String, Object> Criteria, string expression, int page,
                                                  int pageSize)
        {
            var Results = new List<InventoryDTO>();
            using (ITransaction tx = Session.BeginTransaction())
            {
               ICriteria criteria = Session.CreateCriteria<Inventory>()
                    .CreateAlias("Item", "it", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup", "sg", JoinType.InnerJoin)
                    .CreateAlias("Item.SubGroup.Group", "g", JoinType.InnerJoin)
                    .CreateAlias("SatuanPack", "SatuanPack", JoinType.LeftOuterJoin)
                    .CreateAlias("SatuanUnit", "SatuanUnit", JoinType.InnerJoin)
                    .CreateAlias("InventoryWarehouses", "iw", JoinType.InnerJoin)
                    .SetProjection(Projections.ProjectionList()
                                   .Add(Projections.Property("SerialNumber"), "SerialNumber")
                                   .Add(Projections.Property("NoBundle"), "NoBundle")
                                   .Add(Projections.Property("Description"), "Description")
                                   .Add(Projections.Property("Panjang"), "Panjang")
                                   .Add(Projections.Property("Lebar"), "Lebar")
                                   .Add(Projections.Property("Tinggi"), "Tinggi")
                                   .Add(Projections.Property("QtyPack"), "QtyPack")
                                   .Add(Projections.Property("QtyUnit"), "QtyUnit")
                                   .Add(Projections.Property("UnitPrice"), "UnitPrice")
                                   .Add(Projections.Property("CoaHpp"), "CoaHpp")
                                   .Add(Projections.Property("CoaInventory"), "CoaInventory")
                                   .Add(Projections.Property("CoaSales"), "CoaSales")
                                   .Add(Projections.Property("CoaSalesReturn"), "CoaSalesReturn")
                                   .Add(Projections.Property("it.ItemName"), "ItemName")
                                   .Add(Projections.Property("SatuanPack.KodeSatuan"), "SatuanPack")
                                   .Add(Projections.Property("SatuanUnit.KodeSatuan"), "SatuanUnit")
                                   .Add(Projections.Property("g.GroupName"), "Group")
                                   .Add(Projections.Property("sg.SubGroupName"), "Subgroup")
                                   .Add(Projections.Property("iw.KodeWarehouse"), "KodeWarehouse")
                                   .Add(Projections.Property("iw.Qty"), "Qty")
                                   .Add(Projections.Property("iw.Sheet"), "Sheet")
                                   .Add(Projections.Property("iw.KodePallet"), "KodePallet")
                                   .Add(Projections.Property("SatuanUnit.Pengali"), "Pengali")
                                   )
                    .AddOrder(new Order("it.ItemName", true));


                var filter = new List<string>();
                int i = 0;
                foreach (var criterion in Criteria)
                    if (
                        !typeof(Inventory).GetProperties().Any(
                            x => x.Name == criterion.Key.Split(new[] { "." }, StringSplitOptions.None)[0]))
                        filter.Add(criterion.Key);

                filter.ForEach((x) => { Criteria.Remove(x); }
                    );


                dynamic exp = null;
                if (expression == "AND")
                     exp = Expression.Conjunction();
               
                else
                     exp = Expression.Disjunction();

                foreach (var criterion in Criteria)
                {
                    if (criterion.Value == null) continue;

                    if (criterion.Key.Contains("Item"))
                    {
                        if (criterion.Key.Contains("Item.SubGroup"))
                            exp.Add(Expression.Eq("sg.SubGroupID", criterion.Value.ToString()));
                        else
                            exp.Add(Expression.Eq("it.ItemId", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("Warehouse"))
                    {
                        exp.Add(Expression.Eq("iw.KodeWarehouse", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("SatuanUnit"))
                    {
                        exp.Add(Expression.Eq("SatuanUnit.KodeSatuan", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("SatuanPack"))
                    {
                        exp.Add(Expression.Eq("SatuanPack.KodeSatuan", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Key.Contains("KodePallet"))
                    {
                        exp.Add(Expression.Eq("iw.KodePallet", criterion.Value.ToString()));
                        continue;
                    }

                    if (criterion.Value.GetType() == typeof(string))
                        exp.Add(Expression.Like(criterion.Key, criterion.Value.ToString(), MatchMode.Anywhere));
                    else
                    {
                        if (criterion.Key == "IsStock")
                            exp.Add(Expression.Conjunction().Add((Expression.Eq(criterion.Key, criterion.Value))));
                        else
                            exp.Add(Expression.Eq(criterion.Key, criterion.Value));
                    }
                    i += 1;
                }

                criteria.Add(exp);
                var Res = criteria.List();
                Dictionary<string, string> dicts = new Dictionary<string, string>();
                foreach (object o in (IList)Res)
                {

                    if (!dicts.ContainsKey((o as object[])[0].ToString()))
                    {
                        dicts.Add((o as object[])[0].ToString(), (o as object[])[0].ToString());

                        InventoryDTO inv = new InventoryDTO();
                        inv.SerialNumber = (o as object[])[0].ToString();
                        inv.NoBundle = (int)(o as object[])[1];
                        inv.Description = (String)(o as object[])[2];
                        inv.Panjang = (Decimal)(o as object[])[3];
                        inv.Lebar = (Decimal)(o as object[])[4];
                        if ((o as object[])[20] != null && (Decimal)(o as object[])[20] > 0)
                            inv.Tinggi = (Decimal)(o as object[])[20];
                        else
                            inv.Tinggi = (Decimal)(o as object[])[5];
                        inv.QtyPack = (Decimal)(o as object[])[6];
                        if ((o as object[])[19] != null && (Decimal)(o as object[])[19] > 0)
                            inv.QtyUnit = (Decimal)(o as object[])[19];
                        else
                            inv.QtyUnit = (Decimal)(o as object[])[7];
                        inv.UnitPrice = (Decimal)(o as object[])[8];
                        inv.CoaHpp = (String)(o as object[])[9];
                        inv.CoaInventory = (String)(o as object[])[10];
                        inv.CoaSales = (String)(o as object[])[11];
                        inv.CoaSalesReturn = (String)(o as object[])[12];
                        inv.Item = new ItemDTO()
                        {
                            ItemName = (String)(o as object[])[13],
                            SubGroup = new SubGroupDTO()
                            {
                                SubGroupName = (String)(o as object[])[17],
                                Group = new GroupDTO() { GroupName = (String)(o as object[])[16] }
                            }
                        };
                        inv.SatuanPack = new SatuanDTO() { KodeSatuan = (String)(o as object[])[14] };
                        inv.SatuanUnit = new SatuanDTO() { KodeSatuan = (String)(o as object[])[15], Pengali = (o as object[])[22] == null ? 1 : (Decimal)(o as object[])[22] };
                        inv.Warehouses = new ObservableCollection<WarehouseDTO>(){ new WarehouseDTO()
                                                                                       {
                                                                                           //SerialNumber = (o as object[])[0].ToString(), 
                                                                                           KodeWarehouse = (String)(o as object[])[18],
                                                                                           Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                                                                                           Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                                                                                           KodePallet = (String)(o as object[])[21]
                                                                                       } };
                        Results.Add(inv);
                    }
                    else
                    {
                        Results.LastOrDefault().Warehouses.Add(new WarehouseDTO()
                        {
                            //SerialNumber = (o as object[])[0].ToString(),
                            KodeWarehouse = (String)(o as object[])[18],
                            Qty = (o as object[])[19] == null ? 0 : (Decimal)(o as object[])[19],
                            Sheet = (o as object[])[20] == null ? 0 : (Decimal)(o as object[])[20],
                            KodePallet = (String)(o as object[])[21]
                        });
                    }
                }
                RecordCount = 0;
                tx.Commit();
            }

          
            return Results;
        }





        //Untuk searching Inventory di setiap transaksi
        public IList<Inventory> GetDatabyCriteriaSQL(Dictionary<String, Object> criteria, string expression, int page,
                                                  int pageSize)
        {
            IList<Inventory> Results = new List<Inventory>();
            using (ITransaction tx = Session.BeginTransaction())
            {
                var sb2 = new StringBuilder();
                if (criteria.Count > 0) sb2.Append(" Where ");
                int i = 0;
                var filter = new List<string>();
                foreach (var criterion in criteria)
                    if (
                        !typeof(Inventory).GetProperties().Any(
                            x => x.Name == criterion.Key.Split(new[] { "." }, StringSplitOptions.None)[0]))
                        filter.Add(criterion.Key);

                filter.ForEach((x) => { criteria.Remove(x); }
                    );

                foreach (var criterion in criteria)
                {
                    if (criterion.Value == null) continue;
                    if (criterion.Value.GetType() == typeof(string))
                        sb2.AppendFormat(criterion.Key + " like'%{0}%' ", criterion.Value);
                    else
                        sb2.AppendFormat(criterion.Key + "='{0}' ", criterion.Value);

                    i += 1;
                    if (i < criteria.Count)
                    {
                        sb2.Append(criteria.ToList()[i].Key == "IsStock" ? " AND " : expression);
                    }
                }

                StringBuilder sb = new StringBuilder();
                sb.Append("Select * from  ");
                sb.Append("( ");
                sb.Append("SELECT ");
	            sb.Append("i.SerialNumber,NoBundle,i.[Description], QtyPack,  ");
	            sb.Append("i.Panjang,i.Lebar,iw.Sheet,KodeMasuk,iw.Qty,i2.ItemName, iw.KodePallet,i.SatuanPack, ");
	            sb.Append("s.KodeSatuan AS SatuanUnit,s.Pengali, i.IsStock,sg.SubGroupName,g.GroupName,w.WarehouseName,i.ItemId AS Item, ");
	            sb.Append("sg.SubGroupID AS Subgroup,g.GroupID AS [Group] ");
	            sb.Append("FROM inventory i  LEFT JOIN Item i2 ON i.ItemID = i2.ItemID   ");
	            sb.Append("LEFT JOIN Satuan s ON i.SatuanUnit = s.KodeSatuan  ");
	            sb.Append("LEFT JOIN SubGroup sg ON sg.SubGroupID = i2.SubGroupID "); 
	            sb.Append("LEFT JOIN Groups g ON sg.GroupID = g.GroupID  ");

                if (sb2.ToString().Contains("IsStock='True'"))
                    sb.Append("INNER JOIN InventoryWarehouse iw ON i.SerialNumber = iw.SerialNumber ");
                else
                     sb.Append("LEFT JOIN InventoryWarehouse iw ON i.SerialNumber = iw.SerialNumber ");

                sb.Append("INNER JOIN Warehouse w ON iw.KodeWarehouse = w.KodeWarehouse ");
                sb.Append(")X ");

                var query = Session.CreateSQLQuery(sb.ToString() + sb2.ToString());
                var res = query.AddEntity(typeof (Inventory)).List();
                //foreach (object re in res as IList)
                //{
                //    var inventory  = new Inventory();
                //    inventory.SerialNumber = (re as IList)[0].ToString();
                //    inventory.NoBundle = Convert.ToInt32((re as IList)[1]);
                //    inventory.Description = (re as IList)[2].ToString();
                //    inventory.QtyPack = Convert.ToInt32((re as IList)[3]);
                //    inventory.Panjang = Convert.ToDecimal((re as IList)[4]);
                //    inventory.Lebar = Convert.ToDecimal((re as IList)[5]);
                //    inventory.Tinggi = Convert.ToDecimal((re as IList)[6]);
                //    inventory.KodeMasuk = (re as IList)[7].ToString();
                //    inventory.QtyUnit = Convert.ToDecimal((re as IList)[8]);
                //    inventory.KodePallet = 
                //    inventory.Item = new Item() {ItemName = (re as IList)[8].ToString();}
                //}

                RecordCount = res.Count;
                tx.Commit();
            }

            return Results;
        }

        public IList<Inventory> GetDataPaged2(int page, int pageSize)
        {
            DA = new DataAccess<Inventory>();
            List<Inventory> Res = DA.LoadData(new Inventory(), "", page, "usp_GetInventoryList", pageSize);
            RecordCount = DA.TotalRecord;
            return Res;
        }

        public void UpdateUnitPrice(Inventory inventory)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Update Inventory set UnitPrice={0} where SerialNumber='{1}'", inventory.UnitPrice,
                            inventory.SerialNumber);
            int res = Session.CreateSQLQuery(sb.ToString()).ExecuteUpdate();
        }


        public override void Delete(Inventory inventory)
        {
            Session.Delete(inventory);
            
        }

        public void InsertRange(List<Inventory> inventories)
        {
            
            using (var tx = Session.BeginTransaction())
            {
                foreach (var inventory in inventories)
                {
                  
                    var mutasiStok = new MutasiStok();
                    mutasiStok.ReferenceNumber = inventory.KodeMasuk;
                    mutasiStok.SerialNumber = inventory.SerialNumber;
                    mutasiStok.TglMutasi = DateTime.Now;
                    mutasiStok.Type = "SaldoAwal";
                    mutasiStok.QtyMasuk = inventory.QtyUnit ?? 0;
                    mutasiStok.QtySisa = mutasiStok.QtyMasuk;
                    mutasiStok.SheetMasuk = inventory.Tinggi ?? 0;
                    mutasiStok.SheetSisa = mutasiStok.SheetMasuk;
                    mutasiStok.KodeWarehouse =
                        inventory.InventoryWarehouses.FirstOrDefault().
                            KodeWarehouse;
                    if (Session.Get(typeof(Inventory),inventory.SerialNumber) == null)
                            Session.Save(inventory);
                    if (mutasiRepository.GetDataOne(x => x.SerialNumber == mutasiStok.SerialNumber && x.KodeWarehouse == mutasiStok.KodeWarehouse && x.Type == mutasiStok.Type) == null)
                         Session.Save(mutasiStok);
                    if (iwRepository.GetDataOne(x => x.SerialNumber == inventory.SerialNumber && x.KodeWarehouse == inventory.InventoryWarehouses.FirstOrDefault().KodeWarehouse) == null)
                            Session.Save(inventory.InventoryWarehouses.FirstOrDefault());

                }
                    

                tx.Commit();
            }
        }


        public void UpdateHpp(IList<Inventory> inventories)
        {
            StringBuilder sb = new StringBuilder();
            
            //using (var tx = Session.BeginTransaction())
            //{

                foreach (var inventory in inventories)
                {
                    sb.AppendFormat("Update Inventory set UnitPrice={0} where SerialNumber='{1}';", inventory.UnitPrice,
                                inventory.SerialNumber);
                }

                int res = Session.CreateSQLQuery(sb.ToString()).ExecuteUpdate();
            //    tx.Commit();
            //}
           
        }


        public IList<Inventory> GetInventoryList(List<String> serialNumbers)
        {
            var multiCriterias = Session.CreateMultiCriteria();
            IList<Inventory> inventories = new List<Inventory>();
            foreach (var serialNumber in serialNumbers)
            {
                var criteria = Session.CreateCriteria(typeof (Inventory))
                    .Add(Restrictions.Eq("SerialNumber", serialNumber));
                multiCriterias.Add(criteria);
            }

            var results = multiCriterias.List();

            foreach (var result in results)
                inventories.Add((Inventory) ((ArrayList)result)[0]);

            return inventories;

        }
     
    }
}